<html>
<body>
	<table border="1">
		<tr>
			<td valign="top">
				<%-- -------- Include menu HTML code -------- --%> <jsp:include
					page="index.html" />
			</td>
			<td><%@ page language="java" import="java.sql.*,java.util.*"%>
			<%@ page import="javax.sql.rowset.CachedRowSet,com.sun.rowset.CachedRowSetImpl;" %>
				<%
					Connection conn = null;
					try {
						// Load Oracle Driver class file
						DriverManager
								.registerDriver(new com.microsoft.sqlserver.jdbc.SQLServerDriver());

						// Make a connection to the MS SQL database "cse132b"
						conn = DriverManager
								.getConnection(
										"jdbc:sqlserver://localhost\\SQLEXPRESS;databaseName=cse132b",
										"cse132b", "cse132b");
	
						String action = request.getParameter("action");
						// Check if an insertion is requested
						if (action != null && action.equals("insert")) {
							// Begin transaction
							conn.setAutoCommit(false);
							
							PreparedStatement pstmt = conn
									.prepareStatement("INSERT INTO student_enrollment VALUES (?, ?, ?)");
							pstmt.setString(1, request.getParameter("student_id")); 
							pstmt.setString(2, request.getParameter("quarter"));
							pstmt.setString(3, request.getParameter("year"));
							pstmt.executeUpdate();
							
							// Commit transaction
							conn.commit();
							conn.setAutoCommit(true);
						}
						// Check if an update is requested
						if (action != null && action.equals("update")) {
							// Begin transaction
	                        conn.setAutoCommit(false);
	                     	
							PreparedStatement pstmt = conn.prepareStatement("UPDATE student_enrollment SET quarter = ?, " + 
									"year = ? WHERE student_id = ?");
							pstmt.setString(1, request.getParameter("quarter"));
							pstmt.setString(2, request.getParameter("year"));
							pstmt.setString(3, request.getParameter("student_id"));
							pstmt.executeUpdate();
	                     	
							// Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
						}
						
						// Check if a delete is requested
	                    if (action != null && action.equals("delete")) {
	                    	// Begin transaction
	                        conn.setAutoCommit(false);
	                        
	                        // Create the prepared statement and use it to
	                        // DELETE the student FROM the committee table.
	                        PreparedStatement pstmt = conn.prepareStatement(
	                            "DELETE FROM student_enrollment WHERE student_id = ?");
	                        pstmt.setString(1, request.getParameter("student_id"));
	                        pstmt.executeUpdate();

	                        // Commit transaction
	                        conn.commit();
	                        conn.setAutoCommit(true);
	                    }
						
						// Create the statement
						Statement statement = conn.createStatement();
						
						// Use the created statement to SELECT
						// the grad student attributes.
						//SortedMap<String, String> students = new TreeMap<String, String>();
						ResultSet rsStudent = statement.executeQuery("SELECT student_id, first_name, middle_name, last_name FROM student");
						CachedRowSet cachedStudent = new CachedRowSetImpl();
						cachedStudent.populate(rsStudent);
						//while (rsGrad.next()) {
							//String info = rsGrad.getString("first_name") + " " + rsGrad.getString("last_name");
							//students.put(rsGrad.getString("student_id"), info);
						//}
						
						// SELECT student_enrollment
						ResultSet rsEnrollment = statement.executeQuery("SELECT * FROM student_enrollment, student WHERE student_enrollment.student_id=student.student_id");
						CachedRowSet cachedEnrollment = new CachedRowSetImpl();
						cachedEnrollment.populate(rsEnrollment);
				%>
				<table border="1">
					<tr>
						<th>Student ID</th>
						<th>Name</th>
						<th>Enrolled Quarter</th>
						<th>Enrolled Year</th>
					</tr>
					<tr>
						<form action="student_enrollment.jsp" method="POST">
							<input type="hidden" value="insert" name="action">
							<th>
								<select name="student_id" style="width:200px">
								<%
								while(cachedStudent.next()){
								%>
									<option value=<%=cachedStudent.getString("student_id")%>>
									<%=cachedStudent.getString("student_id")%> 
									<%=cachedStudent.getString("first_name")%> 
									<%=cachedStudent.getString("middle_name")%> 
									<%=cachedStudent.getString("last_name")%> 
									</option>
								<% 	
								}
								%>
								</select>
							</th>
							<th></th>
							<th>
								<select name="quarter" style="width:100px">
								<option value="Spring">Spring</option>
								<option value="Summer">Summer</option>
								<option value="Fall">Fall</option>
								<option value="Winter">Winter</option>
								</select>
							</th>
							<th><input name="year" size="10"></th>
							<th><input type="submit" value="Insert"></th>
						</form>
					</tr>
					<%
						// Iterate over the ResultSet
						while (cachedEnrollment.next()) {
							//PreparedStatement pstmt = conn.prepareStatement(
		                           // "SELECT * FROM student WHERE student_id = ?");
		                    //pstmt.setString(1, cachedEnrollment.getString("student_id"));
							//ResultSet rs  = pstmt.executeQuery();
							String firstNname = cachedEnrollment.getString("first_name");
							String middleNname = cachedEnrollment.getString("middle_name");
							String lastNname = cachedEnrollment.getString("last_name");
					%>
					<tr>
                        <form action="student_enrollment.jsp" method="POST">
                        	<input type="hidden" value="update" name="action">
                        	<input type="hidden" value="<%= cachedEnrollment.getString("student_id")%>" name="student_id">
                        	<td><%= cachedEnrollment.getString("student_id")%></td>
                        	<td><%= firstNname + " " + middleNname + " " + lastNname%></td>
                        	<td>
                        		<select name="quarter" style="width:100px">
								<option value="Spring" <%if(cachedEnrollment.getString("quarter").equals("Spring")) out.println("selected = selected");%>>Spring</option>
								<option value="Summer" <%if(cachedEnrollment.getString("quarter").equals("Summer")) out.println("selected = selected");%>>Summer</option>
								<option value="Fall" <%if(cachedEnrollment.getString("quarter").equals("Fall")) out.println("selected = selected");%>>Fall</option>
								<option value="Winter" <%if(cachedEnrollment.getString("quarter").equals("Winter")) out.println("selected = selected");%>>Winter</option>
								</select>
                        	</td>
							<td><input value="<%= cachedEnrollment.getString("year") %>" name="year" size="10"></td>
                        	<td>
                                <input type="submit" value="Update">
                            </td>
                        </form>
                        <form action="student_enrollment.jsp" method="POST">
                            <input type="hidden" value="delete" name="action">
                            <input type="hidden" 
                                value="<%=cachedEnrollment.getString("student_id") %>" name="student_id">
                            <td>
                                <input type="submit" value="Delete">
                            </td>
                        </form>
                    </tr>
                <%
						}
                %>
				<%
					} catch (SQLException e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					} catch (Exception e) {
						System.out.println(e.getMessage());
						//conn.rollback();
					}
				%>
				</table>
			</td>
		</tr>
	</table>
</body>
</html>